/*==============================================================*/
/* DBMS name:      ORACLE Version 10g                           */
/* Created on:     2017/4/19 16:22:55                           */
/*==============================================================*/


/*==============================================================*/
/* Table: DEP                                                   */
/*==============================================================*/
create table DEP  (
   UUID                 NUMBER                          not null,
   NAME                 VARCHAR2(30),
   TELE                 VARCHAR2(30),
   constraint PK_DEP primary key (UUID)
);

comment on table DEP is
'部门';

comment on column DEP.UUID is
'编号';

comment on column DEP.NAME is
'部门名称';

comment on column DEP.TELE is
'联系电话';

/*==============================================================*/
/* Table: EMP                                                   */
/*==============================================================*/
create table EMP  (
   UUID                 NUMBER                          not null,
   USERNAME             VARCHAR2(15),
   PWD                  VARCHAR2(32),
   NAME                 VARCHAR2(28),
   GENDER               NUMBER,
   EMAIL                VARCHAR2(255),
   TELE                 VARCHAR2(30),
   ADDRESS              VARCHAR2(255),
   BIRTHDAY             DATE,
   DEPUUID              NUMBER,
   constraint PK_EMP primary key (UUID)
);

comment on table EMP is
'员工';

comment on column EMP.UUID is
'编号';

comment on column EMP.USERNAME is
'登陆名';

comment on column EMP.PWD is
'登陆密码';

comment on column EMP.NAME is
'真实姓名';

comment on column EMP.GENDER is
'性别';

comment on column EMP.EMAIL is
'邮件地址';

comment on column EMP.TELE is
'联系电话';

comment on column EMP.ADDRESS is
'联系地址';

comment on column EMP.BIRTHDAY is
'出生年月日';

comment on column EMP.DEPUUID is
'部门编号';

/*==============================================================*/
/* Table: EMP_ROLE                                              */
/*==============================================================*/
create table EMP_ROLE  (
   UUID                 NUMBER                          not null,
   EMPUUID              NUMBER,
   ROLEUUID             NUMBER,
   constraint SYS_C005922 primary key (UUID)
);

comment on table EMP_ROLE is
'员工角色';

comment on column EMP_ROLE.UUID is
'编号';

comment on column EMP_ROLE.EMPUUID is
'员工编号';

comment on column EMP_ROLE.ROLEUUID is
'角色编号';

/*==============================================================*/
/* Table: GOODS                                                 */
/*==============================================================*/
create table GOODS  (
   UUID                 NUMBER                          not null,
   NAME                 VARCHAR2(30),
   ORIGIN               VARCHAR2(30),
   PRODUCER             VARCHAR2(30),
   UNIT                 VARCHAR2(30),
   INPRICE              NUMBER(8,2),
   OUTPRICE             NUMBER(8,2),
   GOODSTYPEUUID        NUMBER,
   constraint PK_GOODS primary key (UUID)
);

comment on table GOODS is
'商品';

comment on column GOODS.UUID is
'编号';

comment on column GOODS.NAME is
'名称';

comment on column GOODS.ORIGIN is
'产地';

comment on column GOODS.PRODUCER is
'厂家';

comment on column GOODS.UNIT is
'计量单位';

comment on column GOODS.INPRICE is
'进货价格';

comment on column GOODS.OUTPRICE is
'销售价格';

comment on column GOODS.GOODSTYPEUUID is
'商品类型';

/*==============================================================*/
/* Table: GOODSTYPE                                             */
/*==============================================================*/
create table GOODSTYPE  (
   UUID                 NUMBER                          not null,
   NAME                 VARCHAR2(30),
   constraint PK_GOODSTYPE primary key (UUID)
);

comment on table GOODSTYPE is
'商品分类';

comment on column GOODSTYPE.UUID is
'商品类型编号';

comment on column GOODSTYPE.NAME is
'商品类型名称';

/*==============================================================*/
/* Table: INVENTORY                                             */
/*==============================================================*/
create table INVENTORY  (
   UUID                 NUMBER                          not null,
   GOODSUUID            NUMBER,
   STOREUUID            NUMBER,
   NUM                  NUMBER,
   TYPE                 CHAR,
   CREATETIME           DATE,
   CHECKTIME            DATE,
   CREATER              NUMBER,
   CHECKER              NUMBER,
   STATE                CHAR,
   REMARK               VARCHAR2(200),
   constraint PK_INVENTORY primary key (UUID)
);

comment on table INVENTORY is
'盘盈盘亏';

comment on column INVENTORY.UUID is
'编号';

comment on column INVENTORY.GOODSUUID is
'商品';

comment on column INVENTORY.STOREUUID is
'仓库';

comment on column INVENTORY.NUM is
'数量';

comment on column INVENTORY.TYPE is
'类型';

comment on column INVENTORY.CREATETIME is
'登记日期';

comment on column INVENTORY.CHECKTIME is
'审核日期';

comment on column INVENTORY.CREATER is
'登记人';

comment on column INVENTORY.CHECKER is
'审核人';

comment on column INVENTORY.STATE is
'状态';

comment on column INVENTORY.REMARK is
'备注';

/*==============================================================*/
/* Table: MENU                                                  */
/*==============================================================*/
create table MENU  (
   MENUID               VARCHAR2(20)                    not null,
   MENUNAME             VARCHAR2(30),
   ICON                 VARCHAR2(20),
   URL                  VARCHAR2(255),
   PID                  VARCHAR2(20),
   constraint PK_MENU primary key (MENUID)
);

comment on table MENU is
'菜单';

comment on column MENU.MENUID is
'菜单ID';

comment on column MENU.MENUNAME is
'菜单名称';

comment on column MENU.ICON is
'图标';

comment on column MENU.URL is
'URL';

comment on column MENU.PID is
'上级菜单ID';

/*==============================================================*/
/* Table: ORDERDETAIL                                           */
/*==============================================================*/
create table ORDERDETAIL  (
   UUID                 NUMBER                          not null,
   GOODSUUID            NUMBER,
   GOODSNAME            VARCHAR2(50),
   PRICE                NUMBER(10,2),
   NUM                  NUMBER,
   MONEY                NUMBER(10,2),
   ENDTIME              DATE,
   ENDER                NUMBER,
   STOREUUID            NUMBER,
   STATE                CHAR,
   ORDERSUUID           NUMBER,
   constraint PK_ORDERDETAIL primary key (UUID)
);

comment on table ORDERDETAIL is
'订单明细';

comment on column ORDERDETAIL.UUID is
'编号';

comment on column ORDERDETAIL.GOODSUUID is
'商品编号';

comment on column ORDERDETAIL.GOODSNAME is
'商品名称';

comment on column ORDERDETAIL.PRICE is
'价格';

comment on column ORDERDETAIL.NUM is
'数量';

comment on column ORDERDETAIL.MONEY is
'金额';

comment on column ORDERDETAIL.ENDTIME is
'结束日期';

comment on column ORDERDETAIL.ENDER is
'库管员';

comment on column ORDERDETAIL.STOREUUID is
'仓库编号';

comment on column ORDERDETAIL.STATE is
'采购：0=未入库，1=已入库  销售：0=未出库，1=已出库';

comment on column ORDERDETAIL.ORDERSUUID is
'订单编号';

/*==============================================================*/
/* Table: ORDERS                                                */
/*==============================================================*/
create table ORDERS  (
   UUID                 NUMBER                          not null,
   CREATETIME           DATE,
   CHECKTIME            DATE,
   STARTTIME            DATE,
   ENDTIME              DATE,
   TYPE                 CHAR,
   CREATER              NUMBER,
   CHECKER              NUMBER,
   STARTER              NUMBER,
   ENDER                NUMBER,
   SUPPLIERUUID         NUMBER,
   TOTALMONEY           NUMBER(10,2),
   STATE                CHAR,
   WAYBILLSN            NUMBER,
   constraint PK_ORDERS primary key (UUID)
);

comment on table ORDERS is
'订单';

comment on column ORDERS.UUID is
'编号';

comment on column ORDERS.CREATETIME is
'生成日期';

comment on column ORDERS.CHECKTIME is
'审核日期';

comment on column ORDERS.STARTTIME is
'确认日期';

comment on column ORDERS.ENDTIME is
'入库或出库日期';

comment on column ORDERS.TYPE is
'1:采购 2:销售';

comment on column ORDERS.CREATER is
'下单员';

comment on column ORDERS.CHECKER is
'审核员';

comment on column ORDERS.STARTER is
'采购员';

comment on column ORDERS.ENDER is
'库管员';

comment on column ORDERS.SUPPLIERUUID is
'供应商或客户';

comment on column ORDERS.TOTALMONEY is
'合计金额';

comment on column ORDERS.STATE is
'采购: 0:未审核 1:已审核, 2:已确认, 3:已入库；销售：0:未出库 1:已出库';

comment on column ORDERS.WAYBILLSN is
'运单号';

/*==============================================================*/
/* Table: RETURNORDERDETAIL                                     */
/*==============================================================*/
create table RETURNORDERDETAIL  (
   UUID                 NUMBER                          not null,
   GOODSUUID            NUMBER,
   GOODSNAME            VARCHAR2(50),
   PRICE                NUMBER(10,2),
   NUM                  NUMBER,
   MONEY                NUMBER(10,2),
   ENDTIME              DATE,
   ENDER                NUMBER,
   STOREUUID            NUMBER,
   STATE                CHAR,
   ORDERSUUID           NUMBER,
   constraint PK_RETURNORDERDETAIL primary key (UUID)
);

comment on table RETURNORDERDETAIL is
'退货订单明细';

comment on column RETURNORDERDETAIL.UUID is
'编号';

comment on column RETURNORDERDETAIL.GOODSUUID is
'商品编号';

comment on column RETURNORDERDETAIL.GOODSNAME is
'商品名称';

comment on column RETURNORDERDETAIL.PRICE is
'价格';

comment on column RETURNORDERDETAIL.NUM is
'数量';

comment on column RETURNORDERDETAIL.MONEY is
'金额';

comment on column RETURNORDERDETAIL.ENDTIME is
'结束日期';

comment on column RETURNORDERDETAIL.ENDER is
'库管员';

comment on column RETURNORDERDETAIL.STOREUUID is
'仓库编号';

comment on column RETURNORDERDETAIL.STATE is
'状态';

comment on column RETURNORDERDETAIL.ORDERSUUID is
'退货订单编号';

/*==============================================================*/
/* Table: RETURNORDERS                                          */
/*==============================================================*/
create table RETURNORDERS  (
   UUID                 NUMBER                          not null,
   CREATETIME           DATE,
   CHECKTIME            DATE,
   ENDTIME              DATE,
   TYPE                 CHAR,
   CREATER              NUMBER,
   CHECKER              NUMBER,
   ENDER                NUMBER,
   SUPPLIERUUID         NUMBER,
   TOTALMONEY           NUMBER(10,2),
   STATE                CHAR,
   WAYBILLSN            NUMBER,
   ORDERSUUID           NUMBER,
   constraint PK_RETURNORDERS primary key (UUID)
);

comment on table RETURNORDERS is
'退货订单';

comment on column RETURNORDERS.UUID is
'编号';

comment on column RETURNORDERS.CREATETIME is
'生成日期';

comment on column RETURNORDERS.CHECKTIME is
'检查日期';

comment on column RETURNORDERS.ENDTIME is
'结束日期';

comment on column RETURNORDERS.TYPE is
'订单类型';

comment on column RETURNORDERS.CREATER is
'下单员';

comment on column RETURNORDERS.CHECKER is
'审核员工编号';

comment on column RETURNORDERS.ENDER is
'库管员';

comment on column RETURNORDERS.SUPPLIERUUID is
'供应商及客户编号';

comment on column RETURNORDERS.TOTALMONEY is
'合计金额';

comment on column RETURNORDERS.STATE is
'订单状态';

comment on column RETURNORDERS.WAYBILLSN is
'运单号';

comment on column RETURNORDERS.ORDERSUUID is
'原订单编号';

/*==============================================================*/
/* Table: ROLE                                                  */
/*==============================================================*/
create table ROLE  (
   UUID                 NUMBER                          not null,
   NAME                 VARCHAR2(30),
   constraint PK_ROLE primary key (UUID)
);

comment on table ROLE is
'角色';

comment on column ROLE.UUID is
'编号';

comment on column ROLE.NAME is
'名称';

/*==============================================================*/
/* Table: ROLE_MENU                                             */
/*==============================================================*/
create table ROLE_MENU  (
   UUID                 NUMBER                          not null,
   ROLEUUID             NUMBER,
   MENUUUID             VARCHAR2(10),
   constraint SYS_C005942 primary key (UUID)
);

comment on table ROLE_MENU is
'角色菜单';

comment on column ROLE_MENU.UUID is
'编号';

comment on column ROLE_MENU.ROLEUUID is
'角色编号';

comment on column ROLE_MENU.MENUUUID is
'菜单编号';

/*==============================================================*/
/* Table: STORE                                                 */
/*==============================================================*/
create table STORE  (
   UUID                 NUMBER                          not null,
   NAME                 VARCHAR2(30),
   EMPUUID              NUMBER,
   constraint PK_STORE primary key (UUID)
);

comment on table STORE is
'仓库';

comment on column STORE.UUID is
'编号';

comment on column STORE.NAME is
'名称';

comment on column STORE.EMPUUID is
'员工编号';

/*==============================================================*/
/* Table: STOREDETAIL                                           */
/*==============================================================*/
create table STOREDETAIL  (
   UUID                 NUMBER                          not null,
   STOREUUID            NUMBER,
   GOODSUUID            NUMBER,
   NUM                  NUMBER,
   constraint PK_STOREDETAIL primary key (UUID)
);

comment on table STOREDETAIL is
'仓库库存';

comment on column STOREDETAIL.UUID is
'编号';

comment on column STOREDETAIL.STOREUUID is
'仓库编号';

comment on column STOREDETAIL.GOODSUUID is
'商品编号';

comment on column STOREDETAIL.NUM is
'数量';

/*==============================================================*/
/* Table: STOREOPER                                             */
/*==============================================================*/
create table STOREOPER  (
   UUID                 NUMBER                          not null,
   EMPUUID              NUMBER,
   OPERTIME             DATE,
   STOREUUID            NUMBER,
   GOODSUUID            NUMBER,
   NUM                  NUMBER,
   TYPE                 CHAR,
   constraint PK_STOREOPER primary key (UUID)
);

comment on table STOREOPER is
'仓库操作记录';

comment on column STOREOPER.UUID is
'编号';

comment on column STOREOPER.EMPUUID is
'操作员工编号';

comment on column STOREOPER.OPERTIME is
'操作日期';

comment on column STOREOPER.STOREUUID is
'仓库编号';

comment on column STOREOPER.GOODSUUID is
'商品编号';

comment on column STOREOPER.NUM is
'数量';

comment on column STOREOPER.TYPE is
'1：入库 2：出库';

/*==============================================================*/
/* Table: SUPPLIER                                              */
/*==============================================================*/
create table SUPPLIER  (
   UUID                 NUMBER                          not null,
   NAME                 VARCHAR2(30),
   ADDRESS              VARCHAR2(100),
   CONTACT              VARCHAR2(30),
   TELE                 VARCHAR2(30),
   EMAIL                VARCHAR2(100),
   TYPE                 CHAR,
   constraint PK_SUPPLIER primary key (UUID)
);

comment on table SUPPLIER is
'供应商';

comment on column SUPPLIER.UUID is
'编号';

comment on column SUPPLIER.NAME is
'名称';

comment on column SUPPLIER.ADDRESS is
'联系地址';

comment on column SUPPLIER.CONTACT is
'联系人';

comment on column SUPPLIER.TELE is
'联系电话';

comment on column SUPPLIER.EMAIL is
'邮件地址';

comment on column SUPPLIER.TYPE is
'1：供应商 2：客户';

--项目sql有点问题，emp_role, role_menu这两种表不应该给uuid字段，保存的时候uuid为NULL无法保存，解决办法是要么删掉这两张表的uuid字段，要么用序列加触发器让主键自增
--实践发现下面的4条语句需要逐条执行，不要一次全选执行。
--序列+触发器
CREATE SEQUENCE seq_emp_role MINVALUE 1 MAXVALUE 99999999 START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE OR REPLACE TRIGGER trg_emp_role
BEFORE INSERT ON emp_role --表名
FOR EACH ROW
BEGIN
SELECT seq_emp_role.nextval INTO :new.uuid FROM dual;   --new 表示新行，uuid， emp_role 的主键字段
END;

--序列+触发器
CREATE SEQUENCE seq_role_menu MINVALUE 1 MAXVALUE 99999999 START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE OR REPLACE TRIGGER trg_role_menu
BEFORE INSERT ON role_menu --表名
FOR EACH ROW
BEGIN
SELECT seq_role_menu.nextval INTO :new.uuid FROM dual;   --new 表示新行，uuid， role_menu 的主键字段
END;